package com.my.ry.common.util;

import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class ExcelUtil {
	
	/**
	 * 创建表头
	 * @param sheetName: sheet名 
	 * @param sheetTitle： sheet列的表头
	 * @return
	 */
	private static HSSFWorkbook makeExcelHead(String sheetName, String[] sheetTitle) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建sheet
		HSSFSheet sheet = workbook.createSheet(sheetName);
		
		sheet.setAutobreaks(true);
		HSSFRow row = sheet.createRow(0);
		// 设置表头字体
		HSSFCellStyle style = setCellStyle(workbook, (short)13, true);        
        
		for (int i = 0; i < sheetTitle.length; i++) {
			// 创建单元格
			HSSFCell cell = row.createCell(i);
			// 设值
			cell.setCellValue(sheetTitle[i]);
			// 设置样式
			cell.setCellStyle(style);
		}
		
		return workbook;
	}
	
	/**
	 * 导出Excel
	 * @param fileName： 导出文件名
	 * @param sheetName： sheet名
	 * @param sheetTitle: sheet列的表头
	 * @param dataList： 导出数据列表
	 * @param beanPropertys： sheet列的表头对应的数据库字段
	 * @param response： 服务器返回类型
	 * @return
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @throws NoSuchMethodException
	 * @throws IOException 
	 */
	public static <T> void exportExcelData(String fileName,
			String sheetName,
			String[] sheetTitle, 
			List<T> dataList, 
			String[] beanPropertys,
			HttpServletResponse response) 
			throws IllegalAccessException, InvocationTargetException, NoSuchMethodException, IOException {
		// 创建Excel工作簿对象
		HSSFWorkbook workbook = makeExcelHead(sheetName, sheetTitle);
		HSSFSheet sheet = workbook.getSheetAt(0);
		
		// 填充数据
		HSSFCellStyle cellStyle = setCellStyle(workbook, (short)11, false);

		int size = dataList.size();
		for (int i = 0; i < size; i++) {
			// 获取每一行
			HSSFRow row = sheet.createRow(i + 1); // +1：第一行写了表头
			T t = dataList.get(i);
			
			for (int j = 0; j < beanPropertys.length; j++) {
				// 获取属性值
				Object value = BeanUtils.getProperty(t, beanPropertys[j]);
				// 创建单元格
				HSSFCell cell = row.createCell(j);
				
				// 给单元格赋值
				cell.setCellValue(value.toString());
				
				// 给单元格设置样式 
				cell.setCellStyle(cellStyle);
			}
		}
		
		// 调整列宽
		changeColumnWidth(sheet, sheetTitle);
		
		// 设置response的Header
		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
		response.setContentType("application/vnd.ms-excel");
		OutputStream out = new BufferedOutputStream(response.getOutputStream());
		workbook.write(out);
		out.flush();
		out.close();
	}
	
	
	/**
	 * 设置单元格样式
	 * @param workbook： 工作簿对象
	 * @param fontSize: 字体大小
	 * @param boolBold: 是否要加粗
	 * @return 单元格样式
	 */
	private static HSSFCellStyle setCellStyle(HSSFWorkbook workbook, short fontSize, boolean boolBold){
		// 设置表头字体
		HSSFCellStyle style = workbook.createCellStyle();    
		if (boolBold) { // 表头居中
			style.setAlignment(HorizontalAlignment.CENTER);
	        style.setVerticalAlignment(VerticalAlignment.CENTER);
		}
        // 字体样式
        HSSFFont font = workbook.createFont();
        // 字体大小
        font.setFontHeightInPoints(fontSize);
        // 加粗
        font.setBold(boolBold);
        style.setFont(font);
        
        // 自动换行
        style.setWrapText(true);
        
        // 设置边框
        // 下边框
        style.setBorderBottom(BorderStyle.THIN); 
        // 左边框
        style.setBorderLeft(BorderStyle.THIN);
        // 右边框
        style.setBorderRight(BorderStyle.THIN);
        // 上边框
        style.setBorderTop(BorderStyle.THIN);
        
        return style;
	}
	
	/**
	 * 根据单元格不同属性返回字符串数值
	 * @param cell
	 * @return
	 */
	public static String getCellStringValue(HSSFCell cell) {
		String cellVal = cell.getStringCellValue();
		switch (cell.getCellTypeEnum()) {
		case STRING:
			cellVal = cell.getStringCellValue();
			if(cellVal.trim().equals("")||cellVal.trim().length()<=0) {
				cellVal = "";
			}
			break;
		case NUMERIC:
			cellVal = String.valueOf(cell.getNumericCellValue());
			break;
		case BLANK:
			cellVal = "";
			break;
		case BOOLEAN:
		case ERROR:
			break;
		default:
			break;
		}
		
		return cellVal;
	}
	
	/**
	 * 调整列宽
	 * @param sheet
	 * @param sheetTitle
	 */
	private static void changeColumnWidth(HSSFSheet sheet, String[] sheetTitle) {
		for (int i = 0; i < sheetTitle.length; i++) {
			// 调整列宽
			sheet.autoSizeColumn(i);
		}
	}
}
